﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using SW_SoftBully.Controllers;
using SW_SoftBully.Models.ModuloAdministracion.AreaComun;
using SW_SoftBully.Models.ModuloAdministracion.Edificio;
using System.Data.SqlClient;
using log4net;

namespace SW_SoftBully.Models.ModuloAdministracion.AreaComun
{
    public class AreaComunDAO
    {

        public string RegistrarAreaComun(AreaComunBean areacomun)
        {
            try
            {
                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["SoftBully"].ConnectionString;

                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);

                sqlCon.Open();

                string commandString = "INSERT INTO Area_Comun (Id_Edificio, Nombre_Area, Piso, Area, Ubicacion, Es_Reservable, Estado)  " +
                                       " VALUES ('" + areacomun.Id_Edificio + "','" + areacomun.Nombre_Area + "','" + areacomun.Piso + "'," + areacomun.Area + ",'" + areacomun.Ubicacion + "'," + areacomun.Es_Reservable + ",1)";

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                sqlCmd.ExecuteNonQuery();

                sqlCon.Close();

                areacomun.conexion = "Bien";
            }
            catch (Exception e) {
                String men = e.ToString();
                areacomun.conexion = men;
            }

            return areacomun.conexion;
        }

        public Boolean ExisteAreaComun(AreaComunBean areacomun)
        {
            return true;
        }

        public List<AreaComunBean> ListarAreaComun(AreaComunBean areacomun)
        {
            try
            {
                List<AreaComunBean> listaareacomun = new List<AreaComunBean>();

                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["SoftBully"].ConnectionString;

                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);

                sqlCon.Open();

                string commandString = "SELECT * FROM Area_Comun WHERE estado = 1";

                if (areacomun.Id_Edificio > 0)
                {
                    commandString += " AND Id_Edificio = " + areacomun.Id_Edificio + "";
                }
                if (!string.IsNullOrEmpty(areacomun.Nombre_Area)) {
                    commandString += " AND Nombre_Area = '" + areacomun.Nombre_Area + "'";
                }
                if (!string.IsNullOrEmpty(areacomun.Piso))
                {
                    commandString += " AND Piso = '" + areacomun.Piso + "'";
                }
                /*if (!string.IsNullOrEmpty(Convert.ToString(areacomun.Area))) {
                    commandString += " AND Area = " + areacomun.Area + "";
                }*/
                if (!string.IsNullOrEmpty(areacomun.Ubicacion)) {
                    commandString += " AND Ubicacion LIKE '%" + areacomun.Ubicacion + "%'";
                }
                if (areacomun.Es_Reservable > 0)
                {
                    commandString += " AND Es_Reservable = " + areacomun.Es_Reservable + "";
                }

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);


                SqlDataReader dataReader = sqlCmd.ExecuteReader();



                while (dataReader.Read()) {
                    AreaComunBean AreaComun = new AreaComunBean();

                    AreaComun.Id_Area_Comun = Convert.ToInt32(dataReader["Id_Area_Comun"]);
                    AreaComun.Id_Edificio = Convert.ToInt32(dataReader["Id_Edificio"]);
                    AreaComun.Nombre_Area = Convert.ToString(dataReader["Nombre_Area"]);
                    AreaComun.Piso = Convert.ToString(dataReader["Piso"]);
                    AreaComun.Area = Convert.ToDouble(dataReader["Area"]);
                    AreaComun.Ubicacion = Convert.ToString(dataReader["Ubicacion"]);
                    AreaComun.Es_Reservable = Convert.ToInt32(dataReader["Es_Reservable"]);

                    SqlConnection sqlCon2 = new SqlConnection(cadenaConfiguracion);

                    sqlCon2.Open();

                    string commandString2 = "SELECT * FROM Edificio WHERE estado= 1 AND Id_Edificio = " + AreaComun.Id_Edificio + "";

                    SqlCommand sqlCmd2 = new SqlCommand(commandString2, sqlCon2);

                    SqlDataReader dataReader2 = sqlCmd2.ExecuteReader();

                    System.Diagnostics.Debug.WriteLine("------------------->" + AreaComun.Id_Edificio);

                    while (dataReader2.Read())
                    {
                        AreaComun.Nombre_Edificio = Convert.ToString(dataReader2["Nombre"]);

                    }

                    dataReader2.Close();
                    sqlCon2.Close();

                    listaareacomun.Add(AreaComun);                    
                }

                dataReader.Close();
                sqlCon.Close();

                return listaareacomun;

            }
            catch (Exception e) {
                String men = e.ToString();
                areacomun.conexion = men;
                System.Diagnostics.Debug.WriteLine("------------------->" + men);
                return null;
            }
        }

        public string ModificarAreaComun(AreaComunBean areacomun)
        {
            try{
                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["SoftBully"].ConnectionString;

                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);

                sqlCon.Open();

                string commandString = "UPDATE Area_Comun SET " +
                                       "Id_Edificio = " + areacomun.Id_Edificio + ", " +
                                       "Nombre_Area = '" + areacomun.Nombre_Area + "', " +
                                       "Piso = '" + areacomun.Piso + "', " +
                                       "Area = " + areacomun.Area + ", " +
                                       "Ubicacion = '" + areacomun.Ubicacion + "', " +
                                       "Es_Reservable = " + areacomun.Es_Reservable + " " +
                                       "WHERE Id_Area_Comun = " + areacomun.Id_Area_Comun + "";

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

                sqlCmd.ExecuteNonQuery();

                sqlCon.Close();

                areacomun.conexion = "Bien";
            }
            catch (Exception e){
                String men = e.ToString();
                areacomun.conexion = men;
            }
            
            return areacomun.conexion;
        }

        public string EliminarAreaComun(AreaComunBean areacomun)
        {
            try
            {
                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["SoftBully"].ConnectionString;

                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);

                sqlCon.Open();

                string commandString = "UPDATE Area_Comun SET Estado = 0 WHERE Id_Area_Comun = " + areacomun.Id_Area_Comun+ "";

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                sqlCmd.ExecuteNonQuery();

                sqlCon.Close();

                areacomun.conexion = "Bien";
            }
            catch (Exception e) {
                String men = e.ToString();
                areacomun.conexion = men;
            }
            return areacomun.conexion;
        }

        public void EliminarAreaComun(EdificioBean edificio)
        {
            try
            {
                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["SoftBully"].ConnectionString;

                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);

                sqlCon.Open();

                string commandString = "UPDATE Area_Comun SET Estado = 0 WHERE Id_Edificio = " + edificio.Id_Edificio + "";


                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                sqlCmd.ExecuteNonQuery();

                sqlCon.Close();
            }
            catch (Exception e)
            {
                String men = e.ToString();
            }
        }

        public void EliminarAreaComunPiso(EdificioBean edificio)
        {
            try
            {
                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["SoftBully"].ConnectionString;

                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);

                sqlCon.Open();

                string commandString = "UPDATE Area_Comun SET Estado = 0 WHERE Id_Edificio = " + edificio.Id_Edificio +
                " AND SUBSTRING(Piso,2,LEN(Piso)- 1) > " + edificio.Num_Pisos + " AND SUBSTRING(Piso,1,1) = 'P'";

                System.Diagnostics.Debug.WriteLine("paso por acá");

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                sqlCmd.ExecuteNonQuery();

                sqlCon.Close();
            }
            catch (Exception e)
            {
                String men = e.ToString();
            }
        }

        public void EliminarAreaComunSotano(EdificioBean edificio)
        {
            try
            {
                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["SoftBully"].ConnectionString;

                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);

                sqlCon.Open();

                string commandString = "UPDATE Area_Comun SET Estado = 0 WHERE Id_Edificio = " + edificio.Id_Edificio + 
                                       " AND SUBSTRING(Piso,2,LEN(Piso)- 1) > " + edificio.Num_Sotanos + " AND SUBSTRING(Piso,1,1) = 'S'";


                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                sqlCmd.ExecuteNonQuery();

                sqlCon.Close();
            }
            catch (Exception e)
            {
                String men = e.ToString();

                System.Diagnostics.Debug.WriteLine(men);
            }
        }

        public List<AreaComunBean> ListarAreaComunCP(AreaComunBean areacomun)
        {
            try
            {
                List<AreaComunBean> listaAreaComun = new List<AreaComunBean>();

                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["SoftBully"].ConnectionString;

                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);

                sqlCon.Open();

                string commandString = "SELECT a.nombre_area, a.id_area_comun " +
                                       "FROM area_comun a " +
                                       "WHERE a.estado = 1 AND a.id_edificio = " + areacomun.Id_Edificio;

                System.Diagnostics.Debug.WriteLine("------------------->" + commandString);

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);


                SqlDataReader dataReader = sqlCmd.ExecuteReader();



                while (dataReader.Read())
                {
                    AreaComunBean AreaComun = new AreaComunBean();
                    AreaComun.Id_Area_Comun = Convert.ToInt32(dataReader["id_area_comun"]);
                    AreaComun.Nombre_Area = Convert.ToString(dataReader["nombre_area"]);
                    listaAreaComun.Add(AreaComun);      
                }

                dataReader.Close();
                sqlCon.Close();

                return listaAreaComun;

            }
            catch (Exception e)
            {
                String men = e.ToString();
                areacomun.conexion = men;
                System.Diagnostics.Debug.WriteLine("------------------->" + men);
                return null;
            }
        }
    }
}
